/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.evangelion.core.daoImpl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.persistence.EntityManager;
import javax.persistence.Query;

import com.evangelion.core.bean.Alumno;
import com.evangelion.core.bean.Datossolicitud;
import com.evangelion.core.bean.Permisomatriculado;
import com.evangelion.core.bean.Permisoposee;
import com.evangelion.core.bean.Seccione;
import com.evangelion.core.dao.AlumnoDao;
import com.evangelion.core.pojo.AlumnoClassPractica;

import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author Kellycasma
 */
@Component
@Transactional
public class AlumnoDaoImpl extends AbstractDAO implements AlumnoDao {

    @Override
    public List<Alumno> searchByNifNombre(Seccione seccion, String valorParametro, int start, int max) {
        String select = "SELECT a from Alumno a ";

        Query q = buildQuery(em, select, seccion, valorParametro);

        q.setFirstResult(start);
        q.setMaxResults(max);
        return q.getResultList();
    }

    @Override
    public int countPage(Seccione seccion, String valor) {
        String select = "SELECT COUNT(a.id) from Alumno a ";

        Query q = buildQuery(em, select, seccion, valor);

        return ((Number) q.getSingleResult()).intValue();
    }

    @Override
    public List<AlumnoClassPractica> findClassPracticas(Long alumnoId) {
        String sqla = "select fa.id_alumno as id_columno, al.nombres as NOMBRES, al.apellidos as APELLIDOS, fa.ccount as ccount "
                + " from ("
                + " select id_alumno, sum(cc) as ccount from "
                + " ("
                + " select id_alumno, case when servicio='1 Practica' then 1 when servicio='5 Practicas' then 5 when servicio='10 Practicas' then 10 else 0 end  as cc, servicio"
                + " from facturas_alumnos  where servicio = '1 Practica' or servicio = '5 Practicas' or servicio = '10 Practicas'"
                + " ) tt group by id_alumno "
                + " ) fa, alumnos al "
                + " where al.id = fa.id_alumno";

        if (null != alumnoId) {
            sqla += " and al.id=" + alumnoId;
        }

        List<Object[]> objects = em.createNativeQuery(sqla).getResultList();

        List<AlumnoClassPractica> practicas = new ArrayList<AlumnoClassPractica>();
        for (Object[] objArr : objects) {
            practicas.add(buildContractedClassPratica(objArr));
        }

        String sqlb = "select id_alumno as id_columno, al.nombres, al.apellidos, count(id_alumno) as ccount from ( "
                + "(select jueves as id_alumno from horario where jueves is not null)  "
                + "union all "
                + "(select lunes as id_alumno from horario where lunes is not null)  "
                + "union all "
                + "(select martes as id_alumno from horario where martes is not null)  "
                + "union all "
                + "(select miercoles as id_alumno from horario where miercoles is not null)  "
                + "union all "
                + "(select viernes as id_alumno from horario where viernes is not null)  "
                + "union all "
                + "(select sabado as id_alumno from horario where sabado is not null)  "
                + ") aa , alumnos al "
                + "where aa.id_alumno = al.id ";
        if (null != alumnoId) {
            sqlb += " and al.id=" + alumnoId;
        }
        sqlb += " group by id_alumno";

        List<Object[]> ppB = em.createNativeQuery(sqlb).getResultList();

        for (Object[] objArr : ppB) {
            AlumnoClassPractica pra = buildConsumedClassPratica(objArr);

            int idx = practicas.indexOf(pra);
            if (idx >= 0) {
                practicas.get(idx).setConsumed(pra.getConsumed());
            } else {
                pra.setContracted(0);
                practicas.add(pra);
            }
        }

        for (AlumnoClassPractica pra : practicas) {
            if (null == pra.getContracted()) {
                pra.setContracted(0);
            }
            if (null == pra.getConsumed()) {
                pra.setConsumed(0);
            }
        }
        return practicas;

    }

    private AlumnoClassPractica buildContractedClassPratica(Object[] acp) {
        AlumnoClassPractica pra = new AlumnoClassPractica();
        pra.setAlumnoId((Integer) acp[0]);
        pra.setNombres((String) acp[1]);
        pra.setApellidos((String) acp[2]);

        pra.setContracted(((BigDecimal) acp[3]).intValue());

        return pra;
    }

    private AlumnoClassPractica buildConsumedClassPratica(Object[] acp) {
        AlumnoClassPractica pra = new AlumnoClassPractica();
        pra.setAlumnoId((Integer) acp[0]);
        pra.setNombres((String) acp[1]);
        pra.setApellidos((String) acp[2]);

        pra.setConsumed(((Long) acp[3]).intValue());

        return pra;
    }

    private Query buildQuery(EntityManager em, String select, Seccione seccion, String valorParametro) {
        Map<String, Object> pMap = new HashMap<String, Object>();
        String sQuery = "";
        if (null == valorParametro || valorParametro.equals("")) {
            sQuery = select + "where 1=1";
        } else {
            int param = -1;
            try {
                param = Integer.parseInt(valorParametro);
                sQuery = select + "WHERE a.id = :valorParametro";
            } catch (Exception e) {
                sQuery = select + "WHERE (a.nif LIKE :valorParametro or a.nombres like :valorParametro or a.apellidos like :valorParametro) ";
            }

            if (param == -1) {
                pMap.put("valorParametro", "%" + valorParametro + "%");
            } else {
                pMap.put("valorParametro", param);
            }

        }
        if (null != seccion) {
            sQuery += " AND a.seccion=:seccion";
            pMap.put("seccion", seccion);
        }

        Query q = em.createQuery(sQuery);
        for (String key : pMap.keySet()) {
            q.setParameter(key, pMap.get(key));
        }
        return q;
    }

    @Override

    public void save(Alumno alumno) {
        super.save(alumno);
    }

    @Override

    public void save(Permisomatriculado permisomatriculado) {
        super.save(permisomatriculado);
    }

    @Override
    public void save(Permisoposee permisoposee) {
        super.save(permisoposee);
    }

    @Override

    public void save(Datossolicitud datossolicitud) {
        super.save(datossolicitud);
    }

    @Override
    public Alumno findObjByParam(String parametro, String valor) {
        try {
            Query q = em.createQuery("SELECT x FROM Alumno x where x." + parametro + "=:valor");
            q.setParameter("valor", valor);
            return (Alumno) q.getSingleResult();
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    public List<Alumno> findListByParam(String parametro, String valor) {
        try {
            Query q = em.createQuery("SELECT x FROM Alumno x where x." + parametro + " like :valor");
            q.setParameter("valor", "%" + valor + "%");
            return q.getResultList();
        } catch (Exception e) {
            return null;
        }
    }
}
